import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllDepartment():
    print('return all department')
    sql = "SELECT department_id, department_name FROM department"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllOccupation():
    print('return all occupation')
    sql = "SELECT occupa_id, occupa_name FROM occupation"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有岗位")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getStaff():
    print('return all staff')
    sql = "SELECT staff_id, staff_name FROM staff WHERE staff_id not in(SELECT staff_id FROM occupastaff)"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有需要分配的员工")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def newocps(id1, id2, id3):
    print('insert a new ocps')

    sql = "insert into occupastaff values( '" + id1 + "', '" + id2 + "','" + id3 + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getOcps(dpmt_id):
    print('select ocps')

    sql = "select os.department_id,department_name,os.occupa_id,occupa_name,os.staff_id,staff_name from occupastaff " \
          "as os left " \
          "join department as d on os.department_id=d.department_id left join occupation as c on " \
          "os.occupa_id=c.occupa_id left join staff as s on os.staff_id=s.staff_id where os.department_id='" + dpmt_id + "'"
    print(sql)
    cursor.execute(sql)
    connect.commit()
    temp = cursor.fetchall()
    if temp == ():
        print("没有人岗")
        return 0
    else:
        print("查询成功")
        return temp


def editocps(id1, id2, id3, id4):
    print('edit ocps')

    sql = "update occupastaff set staff_id='" + id4 + "'where department_id='" + id1 + "' and occupa_id='" + id2 + "' and staff_id='" + id3 + "' "
    print(sql)

    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def deleteOcps(id1, id2, id3):
    sql = "Delete FROM occupastaff WHERE department_id ='" + id1 + "' and occupa_id ='" + id2 + "' and staff_id='" + id3 + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchOcps(word):
    sql = "select os.department_id,department_name,os.occupa_id,occupa_name,os.staff_id,staff_name from occupastaff " \
          "as os left join department as d on os.department_id=d.department_id left join occupation as c on " \
          "os.occupa_id=c.occupa_id left join staff as s on os.staff_id=s.staff_id " \
          "where os.department_id LIKE '%" + word + "%' or os.occupa_id LIKE '%" + word + "%' or os.staff_id LIKE '%" + word + "%' " \
                                                                                                                               "or d.department_name LIKE '%" + word + "%' or c.occupa_name LIKE '%" + word + "%' or s.staff_name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的岗位")
        return 0
    else:
        print("搜索成功")
        return temp


def is003(admin):
    sql = "select occupa_id from occupastaff WHERE staff_id = '" + admin + "'"
    print(sql)
    cursor.execute(sql)

    occupa_id = cursor.fetchall()[0][0]
    print(occupa_id)
    if occupa_id == '003':
        return jsonify(1)
    else:
        return jsonify(0)

